Result of Populated Tables(Staging,Dictionary and MergesFinalDic) 11
* The Zip File, contained the code to removed duplications in each Dictionary Tables after it had been populated.
*In Step 4, the Staging table was populated, the result are as follow;
Using SSIS Toolbox, Drag the data flow task in the control flow dashboard for each dictionary table.
*Each Data Flow Task component is edit for Dic_BriefPerformanceOfMovies
*Code Expression for Derived Column
REPLACE(REPLACE(REPLACE(REPLACE(LifeTimeGross,"–",""),"$",""),",",""),"NULL","")
*GIF below is to Edit Dic_MappingMovies
*GIF below is to Edit Dic_MoviesCollectionData
Derived Column Expression for Opening: REPLACE(REPLACE(REPLACE(REPLACE(Opening,"–",""),"$",""),",",""),"NULL","")
Derived Column Expression for Gross: REPLACE(REPLACE(REPLACE(REPLACE(Gross,"–",""),"$",""),",",""),"NULL","")
Derived Column 1 Expression for Opening: ISNULL(Opening) || Opening == "" ? NULL(DT_CY) : (DT_CY)Opening
Derived Column 1 Expression for Gross: ISNULL(Gross) || Gross == "" ? NULL(DT_CY) : (DT_CY)Gross
Derived Column 1 Expression for ReleaseDate: (DT_STR,8000,1252)(TRIM(ReleaseDate) == "" ? (DT_STR,8000,1252)NULL(DT_STR,8000,1252) : ReleaseDate)
*GIF below is to Edit Dic_MoviesCrew
*GIF below is to Edit Dic_MoviesRating
Derived Column Expression for MetaScore: (DT_STR,8000,1252)(TRIM(MetaScore) == "NA" ? (DT_STR,8000,1252)NULL(DT_STR,8000,1252) : MetaScore)
Derived Column Expression for Revenue: (DT_STR,8000,1252)(TRIM(Revenue) == "NA" ? (DT_STR,8000,1252)NULL(DT_STR,8000,1252) : Revenue)
*GIF below is to Edit Dic_MoviesReleaseID
*GIF below is to Edit Dic_MoviesSummary
Derived Column Expression for Budget: REPLACE(REPLACE(REPLACE(REPLACE(Budget,"–",""),"$",""),",",""),"NULL","")
Derived Column 1 Expression for USDistributor: (DT_STR,8000,1252)(TRIM(USDistributor) == "" ? (DT_STR,8000,1252)NULL(DT_STR,8000,1252) : USDistributor)
Derived Column 1 Expression for Budget: (DT_STR,8000,1252)(TRIM(Budget) == "" ? (DT_STR,8000,1252)NULL(DT_STR,8000,1252) : Budget)
Derived Column 1 Expression for MPAA: (DT_STR,8000,1252)(TRIM(MPAA) == "" ? (DT_STR,8000,1252)NULL(DT_STR,8000,1252) : MPAA)
Populated Dictionary Tables
3. The GIF below, will Edit each component in the Executed SQL Task and Data Flow Task